const Router = require('koa-router');
const {db, Op,QueryTypes, DataTypes} = require("../../models/db");
const tool = require("../../tools/tool");
const router = new Router();
const xlsx = require('xlsx')
const axios = require('axios');
const fs = require('fs');
const path = require('path');
const _ = require("lodash");
const downloadFile = async (fileUrl, outputLocationPath) => {
    const writer = fs.createWriteStream(outputLocationPath);

    return axios({
        method: 'get',
        url: fileUrl,
        responseType: 'stream'
    }).then(response => {
        // Pipe the response data to the file stream
        response.data.pipe(writer);

        return new Promise((resolve, reject) => {
            writer.on('finish', resolve);
            writer.on('error', reject);
        });
    });
};
const readExcelFile = (fileName) => {
    const filePath = path.join(__dirname, fileName);
    // Read the file
    const workbook = xlsx.readFile(filePath);

    // Get the first worksheet
    const sheetName = workbook.SheetNames[0];
    const worksheet = workbook.Sheets[sheetName];

    // Convert worksheet to JSON
    const jsonData = xlsx.utils.sheet_to_json(worksheet, {
        header: 1,
        defval: "" // default value for empty cells
    });

    // Remove the header row if it exists (assuming the first row is headers)
    if (jsonData.length > 0) {
        const headers = jsonData[0];
        const jsonArray = jsonData.slice(1).map(row => {
            let obj = {};
            headers.forEach((header, i) => {
                obj[header] = row[i] || "";
            });
            return obj;
        });
        return jsonArray;
    }

    return [];
}
const jsonToSheet = (data, sheetName) => {
    const ws = xlsx.utils.json_to_sheet(data);
    return { [sheetName]: ws };
}
const getRandomFloat = (min, max) => {
    return Math.random() * (max - min) + min;
}
const getRandomInt = (min, max) => {
    min = Math.ceil(min); // 向上取整，确保 min 是整数
    max = Math.floor(max); // 向下取整，确保 max 是整数
    return Math.floor(Math.random() * (max - min + 1)) + min;
}
const getBillSets = async (username) => {
    let has = await db.models['pythonSets'].findOne({
        where: {
            username
        }
    })
    if(has) {
        return JSON.parse(has.sets)
    }
    else {
        return ''
    }
}
// 商品系数得出今天要数据的总数据
let min = 0.8
let max = 1.2
let qtyMin = 2 // 生成的最小商品数量
let qtyMax = 30 // 生成的最大商品数据
let minLengthCal = 15 // 总数据除以15得出至少要分配多少个终端
let gutDays = 2 // 间隔天数

function generateRandomArray(length,totalSum) {
    // 初始化数组
    let arr = new Array(length).fill(0);
    let remainingSum = totalSum;
    let min = 0.8
    let max = 1.2

    for (let i = 0; i < arr.length; i++) {
        if(i === arr.length - 1) {
            arr[i] = remainingSum
        }
        else {
            let qty = Math.ceil(Math.floor(remainingSum/(length-i)) * getRandomFloat(min, max))
            remainingSum = remainingSum - qty
            arr[i] = qty
        }
    }
    return arr;
}
const dispatchTodayTask = async (ctx, username) => {
    let ym = tool.getDay()
    // 今天已分配的
    let hasdis = await db.query(`select id,taskId from python_task_today where 
                                         username='${username}' and ym='${ym}' `,{type:QueryTypes.SELECT})
    let taskIds = []
    if(hasdis.length > 0) {
        hasdis.map(row => {
            taskIds.push(row.taskId)
        })
    }
    let exsql = ''
    if(taskIds.length > 0) {
        exsql = ` and a.id not in (${taskIds.join(',')}) `
    }
    let leftSql = `
        SELECT * from (
                          SELECT a.id,a.username,beginDate,endDate,a.buCode,a.buName,invCode,invName,price,remark,
                                 isnull(a.qty,0) qty,isnull(a.send,0) send,isnull(a.terType1,0) terType1,isnull(a.terType2,0) terType2,
                                 isnull(b.qty,0) qtyDone,isnull(b.send,0) sendDone,isnull(b.terType1,0) terType1Done,isnull(b.terType2,0) terType2Done,
                                 isnull(a.qty,0)-isnull(b.qty,0) left_q,isnull(a.send,0)-isnull(b.send,0) left_s,isnull(a.terType1,0)-isnull(b.terType1,0) left_type1,
                                 isnull(a.terType2,0)-isnull(b.terType2,0) left_type2 
                                 from python_task
                                  a left join v_python_tasksum b on a.username=b.username and a.id=b.taskId  
                          where a.stat='正常' and  '${ym}' BETWEEN a.beginDate and a.endDate
                            and a.username='${username}' ${exsql} 
                      ) a where a.left_q> 0 or left_s>0 or left_type1>0 or left_type2>0
                    `

    // 本月还有的单据
    let lists = await db.query(leftSql, {type:QueryTypes.SELECT})
    // 到月底还有多少天
    let todayYm = tool.getDay()
    let todayTaskArr = []
    // // 如果本月还有单据，插入数据
    if(lists.length > 0) {
        for (let i = 0; i < lists.length; i++) {
            let row = lists[i]
            let end = lists[i].endDate?lists[i].endDate:ym
            let days = Math.abs(tool.dayjs(todayYm).diff(end, 'day')) + 1

            let qtyToday = 0
            let qtySend = 0
            let qtyType1 = 0
            let qtyType2 = 0
            let terMin = 0.8
            let terMax = 1.6
            if(tool.xe.toNumber(row.left_q) > 0) {
                qtyToday = Math.ceil((tool.xe.toNumber(row.left_q)/days) * (days === 1?1:getRandomFloat(min, max)))
            }
            if(tool.xe.toNumber(row.left_s) > 0) {
                qtySend = Math.ceil((tool.xe.toNumber(row.left_s)/days) * (days === 1?1:getRandomFloat(min, max)))
            }
            if(tool.xe.toNumber(row.left_type1) > 0) {
                qtyType1 = Math.ceil((tool.xe.toNumber(row.left_type1)/days) * (days === 1?1:getRandomFloat(min, max)))
            }
            if(tool.xe.toNumber(row.left_type2) > 0) {
                qtyType2 = Math.ceil((tool.xe.toNumber(row.left_type2)/days) * (days === 1?1:getRandomFloat(min, max)))
            }
            let buName = row.buName?row.buName:''
            let remark = row.remark?row.remark:''
            let buCode = row.buCode?row.buCode:''
            let invCode = row.invCode?row.invCode:''
            // 先取数量，
            if(buName) {
                if(qtySend > 0) {
                    todayTaskArr.push({
                        username,
                        ym: tool.getDay(),
                        buCode: buCode,
                        buName: buName,
                        invCode: invCode,
                        invName: row.invName,
                        price: row.price,
                        qty: qtySend,
                        remark: remark,
                        taskId: row.id,
                        billNo: '',
                        saved: 0,
                        kind: 'send',
                        terType: row.terType,
                        ct: tool.getTime()
                    })
                }
                if(qtyToday > 0) {
                    todayTaskArr.push({
                        username,
                        ym: tool.getDay(),
                        buCode: buCode,
                        buName: buName,
                        invCode: invCode,
                        invName: row.invName,
                        price: row.price,
                        qty: qtyToday,
                        remark: remark,
                        taskId: row.id,
                        billNo: '',
                        saved: 0,
                        kind: '',
                        terType: row.terType,
                        ct: tool.getTime()
                    })
                }
            }
            else{
                if(qtyToday > 0) {
                    let minter = Math.ceil(qtyToday / qtyMax)
                    let maxter = Math.floor(qtyToday / qtyMin)
                    let ters = arr.length // 要分配的终端数量
                    let sqlTotal = `SELECT count(*) total from (
                                                                select a.* from ( 
                                                                                            select buCode,buName, '0' qty ,'common' kind ,'${row.invCode}' invCode,'${username}' username
                                                                                    FROM python_ter  WHERE  username='${username}' and stat='生效' 
                                                                                                       
                                                                                ) a left join (SELECT * from python_task_today where id in(SELECT max(id) id from python_task_today where username='${username}' GROUP BY username,ym,buCode,invCode)) b
                                                                                              on a.username=b.username and a.buCode=b.buCode and a.invCode=b.invCode
                                                                where DATEDIFF(DAY, CONVERT(DATE,isnull(b.ym,'2025-01-01'),120),CONVERT(Date,GETDATE(),120)) > ${gutDays} 
                                                            ) a `
                    let totalTersModel = await db.query(sqlTotal, {type:QueryTypes.SELECT, plain: true})
                    let terNum =  Math.ceil(((minter + tool.xe.toNumber(totalTersModel.total)>maxter?maxter:tool.xe.toNumber(totalTersModel.total))/2)*getRandomFloat(terMin,terMax))
                    terNum = terNum < 1 ? 1: terNum
                    let sql = `SELECT TOP ${terNum} * from (
                                                                select a.* from (
                                                                                    select buCode,buName, '0' qty ,'common' kind ,'${row.invCode}' invCode,'${username}' username,terType 
                                                                                    FROM python_ter  WHERE  username='${username}' and stat ='生效' 
                                                                                ) a left join (SELECT * from python_task_today where id in(SELECT max(id) id from python_task_today where username='${username}' GROUP BY username,ym,buCode,invCode)) b
                                                                                              on a.username=b.username and a.buCode=b.buCode and a.invCode=b.invCode
                                                                where DATEDIFF(DAY, CONVERT(DATE,isnull(b.ym,'2025-01-01'),120),CONVERT(Date,GETDATE(),120)) > ${gutDays} 
                                                            ) a ORDER BY NEWID();`
                    let terList = await db.query(sql, {type: QueryTypes.SELECT})
                    terNum = terList.length

                    let terQtyArr = generateRandomArray(terNum, qtyToday)
                    // let insertArr = []
                    for (let j = 0; j < terList.length; j++) {
                        if(j <ters) {
                            let qty = tool.xe.toString(terQtyArr[j])
                            if(qty > 0) {
                                todayTaskArr.push({
                                    username,
                                    ym: tool.getDay(),
                                    buCode: terList[j].buCode,
                                    buName: terList[j].buName,
                                    invCode: row.invCode,
                                    invName: row.invName,
                                    price: row.price,
                                    qty: qty,
                                    remark: row.remark?row.remark:'',
                                    taskId: row.id,
                                    billNo: '',
                                    saved: 0,
                                    kind: terList[j].kind,
                                    terType: terList[j].terType,
                                    ct: tool.getTime()
                                })
                            }
                        }
                    }
                }
                if(qtySend > 0) {
                    let minter = Math.ceil(qtySend / qtyMax)
                    let maxter = Math.floor(qtySend / qtyMin)

                    let sqlTotal = `SELECT count(*) total from (
                                                                select a.* from ( 
                                                                                            select buCode,buName, '0' qty ,'send' kind ,'${row.invCode}' invCode,'${username}' username
                                                                                    FROM python_ter  WHERE  username='${username}' and stat='生效'
                                                                                                          
                                                                                ) a left join (SELECT * from python_task_today where id in(SELECT max(id) id from python_task_today where username='${username}' GROUP BY username,ym,buCode,invCode)) b
                                                                                              on a.username=b.username and a.buCode=b.buCode and a.invCode=b.invCode
                                                                where DATEDIFF(DAY, CONVERT(DATE,isnull(b.ym,'2025-01-01'),120),CONVERT(Date,GETDATE(),120)) > ${gutDays} 
                                                            ) a `
                    let totalTersModel = await db.query(sqlTotal, {type:QueryTypes.SELECT, plain: true})
                    let terNum =  Math.ceil(((minter + tool.xe.toNumber(totalTersModel.total)>maxter?maxter:tool.xe.toNumber(totalTersModel.total))/2)*getRandomFloat(terMin,terMax))
                    terNum = terNum < 1 ? 1: terNum
                    let sql = `SELECT TOP ${terNum} * from (
                                                                select a.* from (
                                                                                    select buCode,buName, '0' qty ,'send' kind ,'${row.invCode}' invCode,'${username}' username,terType 
                                                                                    FROM python_ter  WHERE  username='${username}' and stat='生效' 

                                                                                ) a left join (SELECT * from python_task_today where id in(SELECT max(id) id from python_task_today where username='${username}' GROUP BY username,ym,buCode,invCode)) b
                                                                                              on a.username=b.username and a.buCode=b.buCode and a.invCode=b.invCode
                                                                where DATEDIFF(DAY, CONVERT(DATE,isnull(b.ym,'2025-01-01'),120),CONVERT(Date,GETDATE(),120)) > ${gutDays} 
                                                            ) a ORDER BY NEWID();`
                    let terList = await db.query(sql, {type: QueryTypes.SELECT})
                    terNum = terList.length

                    let terQtyArr = generateRandomArray(terNum, qtySend)
                    // let insertArr = []
                    for (let j = 0; j < terList.length; j++) {
                        if(j <terNum) {
                            let qty = tool.xe.toString(terQtyArr[j])
                            if(qty > 0) {
                                todayTaskArr.push({
                                    username,
                                    ym: tool.getDay(),
                                    buCode: terList[j].buCode,
                                    buName: terList[j].buName,
                                    invCode: row.invCode,
                                    invName: row.invName,
                                    price: row.price,
                                    qty: qty,
                                    remark: row.remark?row.remark:'',
                                    taskId: row.id,
                                    billNo: '',
                                    saved: 0,
                                    kind: terList[j].kind,
                                    terType: terList[j].terType,
                                    ct: tool.getTime()
                                })
                            }
                        }
                    }
                }
                if(qtyType1 > 0) {
                    let minter = Math.ceil(qtyType1 / qtyMax)
                    let maxter = Math.floor(qtyType1 / qtyMin)
                    let sqlTotal =  `SELECT count(*) total from (
                        select a.* from (
                                            select buCode,buName, '0' qty ,'terType1' kind ,'${row.invCode}' invCode,'${username}' username
                                            FROM v_python_ter_xy WHERE  username='${username}'
                                        ) a left join (SELECT * from python_task_today where id in(SELECT max(id) id 
                         from python_task_today where username='${username}' GROUP BY username,ym,buCode,invCode)) b
                         on a.username=b.username and a.buCode=b.buCode and a.invCode=b.invCode
                         where DATEDIFF(DAY, CONVERT(DATE,isnull(b.ym,'2025-01-01'),120),CONVERT(Date,GETDATE(),120)) > ${gutDays} 
                     ) a `
                    let totalTersModel = await db.query(sqlTotal, {type:QueryTypes.SELECT, plain: true})

                    let terNum =  Math.ceil(((minter + tool.xe.toNumber(totalTersModel.total)>maxter?maxter:tool.xe.toNumber(totalTersModel.total))/2)*getRandomFloat(terMin,terMax))
                    terNum = terNum < 1 ? 1: terNum
                    let sql =  `SELECT top ${terNum} * from (
                        select a.* from (
                                            select buCode,buName, '0' qty ,'terType1' kind ,'${row.invCode}' invCode,'${username}' username,terType
                                            FROM v_python_ter_xy WHERE  username='${username}'
                         ) a 
                            left join (SELECT * from python_task_today where id in(SELECT max(id) id  from python_task_today where username='${username}' GROUP BY username,ym,buCode,invCode)) b
                         on a.username=b.username and a.buCode=b.buCode and a.invCode=b.invCode
                         where DATEDIFF(DAY, CONVERT(DATE,isnull(b.ym,'2025-01-01'),120),CONVERT(Date,GETDATE(),120)) > ${gutDays} 
                     ) a ORDER BY NEWID();`

                    let terList = await db.query(sql, {type: QueryTypes.SELECT})
                    terNum = terList.length

                    let terQtyArr = generateRandomArray(terNum, qtyType1)

                    // let insertArr = []
                    for (let j = 0; j < terList.length; j++) {
                        if(j <terNum) {
                            let qty = tool.xe.toString(terQtyArr[j])
                            if(qty > 0) {
                                todayTaskArr.push({
                                    username,
                                    ym: tool.getDay(),
                                    buCode: terList[j].buCode,
                                    buName: terList[j].buName,
                                    invCode: row.invCode,
                                    invName: row.invName,
                                    price: row.price,
                                    qty: qty,
                                    remark: row.remark?row.remark:'',
                                    taskId: row.id,
                                    billNo: '',
                                    saved: 0,
                                    kind: terList[j].kind,
                                    terType: terList[j].terType,
                                    ct: tool.getTime()
                                })
                            }
                        }
                    }
                }
                if(qtyType2 > 0) {
                    let minter = Math.ceil(qtyType2 / qtyMax)
                    let maxter = Math.floor(qtyType2 / qtyMin)
                    let sqlTotal =`SELECT count(*) total from (
                                                                select a.* from (
                                                                                    select buCode,buName, '0' qty ,'terType2' kind ,'${row.invCode}' invCode,'${username}' username
                                                                                    FROM v_python_ter_fxy WHERE  username='${username}'  
                                                                                ) a
                                                                    left join (SELECT * from python_task_today where id in(SELECT max(id) id from python_task_today where username='${username}' 
                                                                                GROUP BY username,ym,buCode,invCode)) b
                                                                                              on a.username=b.username and a.buCode=b.buCode and a.invCode=b.invCode
                                                                where DATEDIFF(DAY, CONVERT(DATE,isnull(b.ym,'2025-01-01'),120),CONVERT(Date,GETDATE(),120)) > ${gutDays}
                                                            ) a `
                    let totalTersModel = await db.query(sqlTotal, {type:QueryTypes.SELECT, plain: true})

                    let terNum =  Math.ceil(((minter + tool.xe.toNumber(totalTersModel.total)>maxter?maxter:tool.xe.toNumber(totalTersModel.total))/2)*getRandomFloat(terMin,terMax))
                    terNum = terNum < 1 ? 1: terNum
                    let sql =`SELECT top ${terNum} * from (
                                                                select a.* from (
                                                                                    select buCode,buName, '0' qty ,'terType2' kind ,'${row.invCode}' invCode,'${username}' username,terType 
                                                                                    FROM v_python_ter_fxy WHERE  username='${username}'
                                                                                ) a left join 
                                                                    (SELECT * from python_task_today where id in(SELECT max(id) id from python_task_today where username='${username}' GROUP BY username,ym,buCode,invCode)) b
                                                                                              on a.username=b.username and a.buCode=b.buCode and a.invCode=b.invCode
                                                                where DATEDIFF(DAY, CONVERT(DATE,isnull(b.ym,'2025-01-01'),120),CONVERT(Date,GETDATE(),120)) > ${gutDays}
                        ) a ORDER BY NEWID();`
                    let terList = await db.query(sql, {type: QueryTypes.SELECT})
                    terNum = terList.length
                    let terQtyArr = generateRandomArray(terNum, qtyType2)
                    for (let j = 0; j < terList.length; j++) {
                        if(j <terNum) {
                            let qty = tool.xe.toString(terQtyArr[j])
                            if(qty > 0) {
                                todayTaskArr.push({
                                    username,
                                    ym: tool.getDay(),
                                    buCode: terList[j].buCode,
                                    buName: terList[j].buName,
                                    invCode: row.invCode,
                                    invName: row.invName,
                                    price: row.price,
                                    qty: qty,
                                    remark: row.remark?row.remark:'',
                                    taskId: row.id,
                                    billNo: '',
                                    saved: 0,
                                    kind: terList[j].kind,
                                    terType: terList[j].terType,
                                    ct: tool.getTime()
                                })
                            }
                        }
                    }
                }
            }
        }
    }
    return todayTaskArr
}
// mt5信号
router.post('/app/py', async ctx => {
    // return ctx.err('已暂停服务')
    let formData = ctx.request.body.formData
    let which = ctx.request.body.which?ctx.request.body.which:''
    if(!which) {
        if(formData && formData.which) {
            which = formData.which
        }
    }
    let keyWord = ctx.request.body.keyWord
    let body = ctx.request.body
    let cat = ctx.request.body.cat
    let username = ctx.request.body.usernameAss?ctx.request.body.usernameAss:''
    if(!username) {
        username = ctx.request.body.username?ctx.request.body.username:''
    }
    if(ctx.user && ctx.user.username) {
        username = ctx.user.username
    }
    let lastest_version = '1.0.1'
    if(cat) which = 'dropdown'

    let setjson = {
        "ocr_api_url": "https://api.textin.com/ai/service/v2/recognize/table/multipage",
        "ocr_api_id": "a2249528c0439f39fc5de8479f215c0c",
        "ocr_api_code": "5bf919b0a90aaeb24d2557fc724c3e37",
        "web_local": "http://localhost:5050",
        "web_remote": "https://ps.sanzhiyun.cn",
        "api_local": "http://localhost:996/",
        "api_remote": "https://v5api.sanzhiyun.cn/",
        "tb_cols_len": 8,
        "buName_pre":"客户名称:",
        "buName_end":"客户地址:",
        "billDate_pre": "日期:",
        "billDate_len": 10,
        "bill_pre": "编号:",
        "bill_len": 14,
        "address_pre": "客户地址:",
        "address_end": "收货人:",
        "phone_start": "收货电话:",
        "phone_end": "编号:",
        "play": true,
        "bill_area": 240000,
        "img_ratio": 900,
        "invName": '商品名称'
    }
    setjson = {
        "ocr_api_url": "https://api.textin.com/ai/service/v2/recognize/table/multipage",
        "ocr_api_id": "a2249528c0439f39fc5de8479f215c0c",
        "ocr_api_code": "5bf919b0a90aaeb24d2557fc724c3e37",
        "tb_cols_len": 9,
        "buName_pre":"客户:",
        "buName_end":"电话:",
        "billDate_pre": "日期:",
        "billDate_len": 10,
        "bill_pre": "票号:",
        "bill_len": 13,
        "address_pre": "地址:",
        "address_end": "",
        "phone_start": "电话:",
        "phone_end": "地址:",
        "play": true,
        "bill_area": 240000,
        "img_ratio": 900,
        "invName": '商品名称'
    }
    // 每天开始执行任务前分配当天任务。
    try {
        switch (which) {
            case 'getTodayTask': {
                let ret = await getBillSets(username)
                if(ret) {
                    if(ret.dayMin) min = ret.dayMin
                    if(ret.dayMax) max = ret.dayMax
                    if(ret.billMin) qtyMin = 10
                    if(ret.billMax) qtyMax = ret.billMax
                    if(ret.dayTer) minLengthCal = ret.dayTer
                    if(ret.gutDays) gutDays = ret.gutDays
                }
                // 分配任务
                let todayTaskArr = await dispatchTodayTask(ctx, username)
                if(typeof todayTaskArr === 'object') {

                }
                else {
                    if(!todayTaskArr) return ctx.err('分配失败')
                }
                let returnlist = []
                todayTaskArr.map(row => {
                    row.id = 0
                    returnlist.push(row)
                })
                let uuid = tool.uuid()
                returnlist.map(row => {
                    row.uuid = uuid
                })
                await db.query(`delete from python_task_temp where username='${username}'`)
                await db.transaction(async () => {
                    let size = 900
                    let pages = Math.ceil(returnlist.length / size)
                    for (let i = 0; i < pages; i++) {
                        let skip = i * size;
                        let list = returnlist.slice(skip, skip+size)
                        let ret = []
                        for (let j = 0; j < list.length; j++) {
                            let row = list[j]
                            ret.push({
                                username: username,
                                ym: tool.getDay(),
                                buCode: row.buCode,
                                buName: row.buName,
                                invCode:row.invCode,
                                invName: row.invName,
                                price: tool.xe.toString(row.price),
                                qty: tool.xe.toString(row.qty),
                                remark: row.remark,
                                taskId: tool.xe.toNumber(row.taskId),
                                billNo: '',
                                saved: 0,
                                kind: row.kind,
                                uuid: row.uuid,
                            })
                        }
                        await db.models['pythonTaskTemp'].bulkCreate(ret)
                    }
                })
                let resSql = `select distinct a.* from python_task_today a inner join python_task b on a.taskId=b.id 
 and b.stat='正常' and a.username='${username}' and a.saved=0 `
                let res = await db.query(resSql, {type: QueryTypes.SELECT})
                return ctx.suc('', {uuid,res})
            }
            case 'todayTaskDone': {
                let currentPage = body.currentPage?body.currentPage:1
                let pageSize = body.pageSize?body.pageSize:500
                let skip = (currentPage - 1) * pageSize
                let beginDate = formData.beginDate?formData.beginDate:''
                let endDate = formData.endDate?formData.endDate:''
                let buName = formData.buName?formData.buName:''
                let invName = formData.invName?formData.invName:''
                let dataCol = ` a.*,iif(a.saved=1,'已出库', '') stat,b.terType  `
                let total = ` count(*) total `
                let listsql = ` from python_task_today a left join python_ter b on a.buCode=b.buCode where   a.username='${username}' and a.saved=1  ` //
                if(beginDate) {
                    listsql = listsql + ` and '${beginDate}'<=a.ym `
                }
                if(endDate) {
                    listsql = listsql + ` and '${endDate}'>=a.ym `
                }
                if(buName) {
                    listsql = listsql + ` and a.buName='${buName}' `
                }
                if(invName) {
                    listsql = listsql + ` and a.invName='${invName}' `
                }
                let totalSql = `select ` + total + listsql
                let totalModel = await db.query(totalSql, {type: QueryTypes.SELECT, plain: true})


                listsql = 'select '+ dataCol + listsql + ` order by billNo offset ${skip} rows fetch next ${pageSize} rows only ;`
                // 发现还没分配今日任务,分配任务到python_task_tody表里面
                let list = await db.query(listsql,{type: QueryTypes.SELECT})

                return ctx.suc('', {total:totalModel.total, list})
            }
            case 'getBillSets':{
                let has = await db.models['pythonSets'].findOne({
                    where: {
                        username
                    }
                })
                return ctx.suc('', has?has:'')
            }
            // 出库随机设置
            case 'saveBillSets':{
                let has = await db.models['pythonSets'].findOne({
                    where: {
                        username
                    }
                })
                if(has) {
                    has.sets = JSON.stringify(formData)
                }
                else {
                    let model = db.models['pythonSets'].build()
                    model.username = username
                    model.sets = JSON.stringify(formData)
                    await model.save()
                }
                return ctx.suc('保存成功')
            }
            case 'saveTodayTask': {
                try {
                    let tempUuid = ctx.request.body.tempUuid
                    if(!tempUuid) return ctx.err('没有任务')
                    let ct = tool.getTime()
                    let sql = `insert into python_task_today (username,ym,buCode,buName,invCode,invName,
                               price,qty,remark,taskId,billNo,saved,kind,ct)
                               select username,ym,buCode,buName,invCode,invName,
                                      price,qty,remark,taskId,billNo,saved,kind,'${ct}' ct 
                                      from python_task_temp where uuid='${tempUuid}' `
                    await db.transaction(async () => {
                        await db.query(sql, {type: QueryTypes.INSERT})
                        await db.query(`delete from python_task_temp where uuid='${tempUuid}'`)
                    })
                    return ctx.suc('成功生成任务')
                }
                catch (e) {
                    return ctx.err('生成任务失败')
                }
            }
            case 'orders':{

                let list = await db.query(
                    `select a.* from python_task_today a inner join python_task b on a.taskId=b.id 
         where a.username='${username}' and a.saved=0 and b.stat='正常' 
                and a.ym<='${tool.getDay()}' order by  a.id asc `,{type: QueryTypes.SELECT})

                let infos = []
                for (let i = 0; i < list.length; i++) {
                    if (tool.xe.toNumber(list[i].qty) > 0) {
                        let it = {
                            id: list[i].id,
                            buName: list[i].buName,
                            buCode: list[i].buCode,
                            invName: list[i].invName,
                            invCode: list[i].invCode,
                            qty: tool.xe.toNumber(list[i].qty),
                            send: '',
                            price: tool.xe.toNumber(list[i].price),
                            remark: list[i].remark,
                        }
                        if(list[i].kind === 'send') {
                            it.send = '促销赠酒-活动赠酒'
                        }
                        infos.push(it)
                    }
                }
                let group = tool.xe.groupBy(infos, 'buCode')

                let res = []
                for (let it in group) {
                    let oinfo = {
                        intval: 2,//Math.ceil(Math.random()*20)+10,
                        buName: '',
                        buCode: '',
                        infos: '',
                    }
                    for (let i = 0; i < group[it].length; i++) {
                        if(group[it][i].buCode === it) {
                            oinfo.buName = group[it][i].buName
                            oinfo.buCode = group[it][i].buCode

                            break
                        }
                    }
                    oinfo.infos = group[it]
                    res.push(oinfo)
                }
                return ctx.suc('', res)
            }
            // 票据保存成功后写入saved并汇总
            case 'saveOrder': {
                let ids = formData.id
                let billNo = formData.billNo
                ids = JSON.parse(ids)
                if(ids.length > 0) {
                    await db.query(`update python_task_today set saved=1,billNo='${billNo}',ct = '${tool.getTime()}'
                             where id in(${ids.join(',')}) and username='${username}'`, {type: QueryTypes.UPDATE})
                }
                return ctx.suc('')

            }
            case 'version':
                return ctx.suc('', {version: lastest_version})
            // 同步终端
            case 'syncTer':{
                try {
                    let fileName = 'terlist.xls'
                    const outputPath = path.join(__dirname, fileName); // Path where you want to save the file
                    downloadFile(formData, outputPath)
                        .then( async () => {
                            formData = readExcelFile(fileName)
                            let pageSize = 900
                            let pages = Math.ceil(formData.length / pageSize)

                            for (let i = 0; i < pages; i++) {
                                let offset = i * pageSize
                                let list = []
                                for (let i = 0; i < formData.length; i++) {
                                    let buCode = formData[i]['终端编码']
                                    let buName = formData[i]['终端名称']
                                    let address = formData[i]['联系地址']
                                    let phone = formData[i]['营业电话']
                                    let coStat = formData[i]['终端合作状态']
                                    let belong = formData[i]['归属状态']
                                    let linkName = formData[i]['联系人']
                                    let isBeer = formData[i]['啤酒终端']
                                    let stat = formData[i]['终端状态']
                                    list.push(` select '${username}' username,'${buCode}' buCode, '${buName}' buName, '${address}' address
                                        , '${phone}' phone, '${coStat}' coStat, '${belong}' belong, '${linkName}' linkName
                                        , '${isBeer}' isBeer, '${stat}' stat, '' terType `)
                                }
                                let arr = list.slice(offset , (offset+pageSize))
                                let merge = ''
                                if(arr.length > 0) {
                                    let union = list.join(' union all ')
                                    merge = `merge into python_ter a using(${union}) b 
                                                on a.username=b.username and a.buCode=b.buCode when matched 
                                                then update set a.buName=b.buName,a.stat=b.stat,a.coStat=b.coStat,a.isBeer=b.isBeer 
                                                when not matched then insert(username,buCode,buName,linkName,phone,address,isBeer,stat,belong,coStat,terType)
                                                values(b.username,b.buCode,b.buName,b.linkName,b.phone,b.address,b.isBeer,b.stat,b.belong,b.coStat,b.terType);`
                                }
                                if(merge) {
                                    await db.query(merge, {type: QueryTypes.UPDATE})
                                }
                            }
                            return ctx.suc('同步终端成功')
                        })
                        .catch(error => {
                            console.error('Error downloading file:', error);
                            return ctx.err('同步终端出错')
                        });
                    return ctx.suc('')
                } catch (error) {
                    return ctx.err('')
                }
            }
            // 同步商品
            case 'syncGoods':{
                try {
                    try {
                        let fileName = 'goods.xls'
                        const outputPath = path.join(__dirname, fileName); // Path where you want to save the file
                        downloadFile(formData, outputPath)
                            .then( async () => {
                                formData = readExcelFile(fileName)
                                let pageSize = 500
                                let pages = Math.ceil(formData.length / pageSize)

                                for (let i = 0; i < pages; i++) {
                                    let offset = i * pageSize
                                    let list = []
                                    for (let i = 0; i < formData.length; i++) {
                                        let invCode = formData[i]['商品编号']
                                        let invName = formData[i]['商品名称']
                                        let unit = formData[i]['单位']
                                        let left = tool.xe.toString(formData[i]['结存'])
                                        list.push(` select '${username}' username,'${invCode}' invCode, '${invName}' invName,  '${unit}' unit,'${left}' leftC `)
                                    }
                                    let arr = list.slice(offset , (offset+pageSize))
                                    let merge = ''
                                    if(arr.length > 0) {
                                        let union = arr.join(' union all ')
                                        merge = `merge into python_goods a using(${union}) b
                                                on a.username=b.username and a.invCode=b.invCode
                                                when matched then update set a.leftC=b.leftC 
                                                when not matched then insert(username,invCode,invName,unit,leftC)
                                                values(b.username,b.invCode,b.invName,b.unit,b.leftC);`
                                    }
                                    if(merge) {
                                        await db.query(merge, {type: QueryTypes.UPDATE})
                                    }
                                }
                                return ctx.suc('同步商品成功')
                            })
                            .catch(error => {
                                console.error('Error downloading file:', error);
                                return ctx.err('同步商品出错')
                            });
                    } catch (error) {
                        console.error('Error downloading file:', error);
                        return ctx.err('同步商品出错')
                    }


                    return ctx.suc('')
                } catch (error) {
                    return ctx.err('')
                }
            }
            // 保存终端类型
            case 'saveTertype':{
                let id = formData.id
                let terType = formData.terType
                let model = await db.models['pythonTer'].findOne({
                    where: {id}
                })
                let ty = ''
                if(terType === 'terType1') {ty = '现饮'}
                if(terType === 'terType2') {ty = '非现'}
                if(model) {
                    model.terType = ty
                    await model.save()
                }
                return ctx.suc('已修改', ty)
            }
            case 'getTerList': {
                let currentPage = body.currentPage?body.currentPage:1
                let pageSize = body.pageSize?body.pageSize:500
                let skip = (currentPage - 1) * pageSize
                let sqls = ` from python_ter where username='${username}' and stat='生效' `
                if(formData.buCode) {
                    sqls = sqls + ` and buCode like '%${tool.xe.trim(formData.buCode)}%' `
                }
                else if(formData.buName) {
                    sqls = sqls + ` and buName like '%${tool.xe.trim(formData.buName)}%' `
                }
                if(body.excel === 1) {
                    let listSql = `select terType 终端类型,buCode 终端编码,buName 终端名称  ${sqls} ` + ` order by buCode  `
                    let list = await db.query(listSql, {type: QueryTypes.SELECT})
                    let filename = '终端-'+username+'.xls'
                    const wb = xlsx.utils.book_new();
                    xlsx.utils.book_append_sheet(wb, jsonToSheet(list, "终端模板")["终端模板"], "终端模板");
                    xlsx.writeFile(wb, tool.fileSaveDir + '/' + filename, { bookType: 'xls', bookSST: false, type: 'binary' });
                    return  ctx.suc('', tool.downUrl + '/web/'  + filename)
                }
                else {
                    let listTotal = `select count(*) total ${sqls} `
                    let totalModel = await db.query(listTotal, {type: QueryTypes.SELECT, plain: true})
                    let total = totalModel.total
                    let listSql = `select *  ${sqls} `
                    listSql = listSql + ` order by buCode offset ${skip} rows fetch next ${pageSize} rows only;`
                    let list = await db.query(listSql, {type: QueryTypes.SELECT})
                    return ctx.suc('', {total, list})
                }
            }
            case 'importTers': {
                const workbook = xlsx.readFile('./uploads/'+formData.filename);
                const sheetName = workbook.SheetNames[0]; // 获取第一个工作表的名字
                const sheet = workbook.Sheets[sheetName]; // 获取第一个工作表
                const jsonData = xlsx.utils.sheet_to_json(sheet); // 将工作表转换为 JSON
                if (jsonData && jsonData.length === 0) return ctx.err('没有数据')
                let list = []
                for (let i = 0; i < jsonData.length; i++) {
                    if(tool.xe.trim(jsonData[i]['终端类型']) === '现饮' || tool.xe.trim(jsonData[i]['终端类型']) === '非现' || tool.xe.trim(jsonData[i]['终端类型']) === '') {

                    }
                    else {
                        return ctx.err('第 ' + (i+1) +'行 终端类型只能是 现饮 非现 或留宿')
                    }
                }
                for (let i = 0; i < jsonData.length; i++) {
                    if(i > 0 && i%900 === 0) {
                        if(list.length > 0) {
                            let union = list.join(' union ')
                            let merge = ` merge into python_ter a using (${union}) b on a.username=b.username and a.buCode=b.buCode 
                            when matched then update set a.terType=b.terType ;`
                            await db.query(merge, {type: QueryTypes.UPDATE})
                            list = []
                        }
                    }
                    let buCode = tool.xe.trim(tool.xe.toString(jsonData[i]['终端编码']))
                    let terType = tool.xe.trim(tool.xe.toString(jsonData[i]['终端类型']))
                    if(buCode) {
                        list.push(` select  '${username}' username, '${buCode}' buCode,'${terType}' terType `)
                    }

                    if (i === jsonData.length -1 && list.length > 0) {
                        let union = list.join(' union ')
                        let merge = ` merge into python_ter a using (${union}) b on a.username=b.username and a.buCode=b.buCode 
                            when matched then update set a.terType=b.terType ;`
                        await db.query(merge, {type: QueryTypes.UPDATE})
                    }
                }
                return  ctx.suc('导入成功')
            }
            // 下载任务模板
            case 'taskmoban': {
                let beg = formData.beg
                let end = formData.end
                // if(!formData) return ctx.err('没有上传数据')
                // let arrs = formData.split(',')
                // if(arrs.length === 3) {
                //     beg = arrs[1]
                //     end = arrs[2]
                // }
                let def = {'开始日期': '','结束日期': '','终端编码': '','终端名称': '', '商品编码': '','商品名称':'','价格': '','结存':'','出到通用的数量':'','出到现饮的数量':'','出到非现饮的数量':'','出到赠送的数量':'', '备注': ''}
                let goods = await db.query(`select * from python_goods where username='${username}'`,{type: QueryTypes.SELECT})
                let arr = []
                if(beg && end) {
                    let b = tool.dayjs(beg)
                    let e = tool.dayjs(end)
                    if(e.isBefore(b)) {
                        return ctx.err('结束日期不能小于开始日期')
                    }
                }
                else {
                    return  ctx.err('没有日期')
                }
                for (let i = 0; i < goods.length; i++) {
                    let row = {
                        '开始日期': beg,
                        '结束日期': end,
                        '终端编码': '',
                        '终端名称': '',
                        '商品编码': goods[i].invCode,
                        '商品名称': goods[i].invName,
                        '价格': '',
                        '结存': goods[i].leftC,
                        '出到通用的数量': '',
                        '出到现饮的数量': '',
                        '出到非现饮的数量': '',
                        '出到赠送的数量': '',
                        '备注': '',
                    }
                    arr.push(row)
                }
                // 下载填写模板
                let list = [
                    {
                        sheetName: '任务模板',
                        list: arr
                    },
                    {
                        sheetName: '填写示例',
                        list: [
                            {'开始日期': '2025-01-01','结束日期': '2025-04-01','终端编码':'为了搜索准确唯一，建议填写','终端名称': '目标系统中的准确终端名称','商品编码':'为了搜索准确唯一，建议填写', '商品名称': '目标系统中的准确商品名称','价格': '35.5','出到通用的数量':'200', '出到现饮的数量':'2','出到非现饮的数量': '3','出到赠送的数量':'1','备注': '有就填写'},
                        ]
                    },
                    {
                        sheetName: '模板说明',
                        list:[
                            {'字段': '开始日期', '说明':'任务开始时间 格式为 2025-xx-xx 如 2025-01-01'},
                            {'字段': '结束日期', '说明':'任务结束时间 格式为 2025-xx-xx 如 2025-01-01'},
                            {'字段': '终端编码', '说明':'要出库到的终端编码,如果不填写将随机分配终端'},
                            {'字段': '商品编码', '说明':'要出库的商品编码,如果不填写则按照名称查询，选择查询到的第一条记录'},
                            {'字段': '商品名称', '说明':'要出库的商品名称，如不填写则按商品编码查询，查询出多条记录默认选择第一条'},
                            {'字段': '价格', '说明':'商品出库价格'},
                            {'字段': '出到通用的数量', '说明':'当不分现饮与非现饮时,不填写终端时随机在所有终端中选择,时间段内要出库的数量 ,每天的任务数量为余下数量除以当天到任务结束日期的天数'},
                            {'字段': '出到现饮的数量', '说明':'现饮终端要出库的数量，当没有填写终端时，随机分配到现饮终端'},
                            {'字段': '出到非现饮的数量', '说明':'非现饮终端要出库的数量，当没有填写终端时，随机分配到非现饮终端'},
                            {'字段': '出到赠送的数量', '说明':'任务时间内要赠送的数量'},
                            {'字段': '备注', '说明':'如果备注可填写'},
                        ]
                    }
                ]
                try{
                    let filename = '任务模板'+username+'.xls'
                    const wb = xlsx.utils.book_new();
                    xlsx.utils.book_append_sheet(wb, jsonToSheet(list[0].list, "任务模板")["任务模板"], "任务模板");
                    xlsx.utils.book_append_sheet(wb, jsonToSheet(list[1].list, "填写示例")["填写示例"], "填写示例");
                    xlsx.utils.book_append_sheet(wb, jsonToSheet(list[2].list, "模板说明")["模板说明"], "模板说明");
                    xlsx.writeFile(wb, tool.fileSaveDir + '/' + filename, { bookType: 'xls', bookSST: false, type: 'binary' });
                    return  ctx.suc('', tool.downUrl + '/web/'  + filename)

                }
                catch (e) {
                    return ctx.err('导入失败')
                }

                // const buffer = xlsx.build(list)
                // let filename = (params.fileName?(params.fileName+'-'):'导出文件-') + ctx.user.id.toString() + _.now() + '.xlsx'
                // await fs.writeFileSync(tool.fileSaveDir + '/' + filename, buffer)
                // return  ctx.suc('', tool.downUrl + '/web/'  + filename)
                // await fs.writeFileSync(tool.fileSaveDir + '/' + filename, buffer)

            }
            // 保存日志
            case 'saveLog': {
                let stat = formData.stat
                let info = formData.info
                let model = db.models['pythonLog'].build()
                model.username = username
                model.stat = stat
                model.info = info
                model.ctime = tool.getTime()
                await model.save()
                return ctx.suc('')
            }
            //
            case 'getLogList': {
                let sql = `select * from python_log where username='${username}' and
                    SUBSTRING(ctime, 1, 10)='${tool.getDay()}' order by id desc `
                let list = await db.query(sql, {type: QueryTypes.SELECT})
                return ctx.suc('', list)
            }
            // 清空日志
            case 'clearLog': {
                let sql = `delete from python_log where username='${username}' `
                await db.query(sql, {type: QueryTypes.DELETE})
                return ctx.suc('', [])
            }
            // 刷新
            case 'task': {
                let dy = tool.getDay('',0,'day','YYYYMM')
                let sql = `select * from python_task_today where SUBSTRING(REPLACE(ym, '-',''), 1, 6)='${dy}'
    and username='${username}' `
                let list = await db.query(sql, {type: QueryTypes.SELECT})
                let suc = 0
                let err = 0
                let all = list.length
                for (let i = 0; i < list.length; i++) {
                    if(list[i].saved === 1) {
                        suc = suc + 1
                    }
                    if(list[i].noBuName===1 || list[i].noInvName === 1) {
                        err = err + 1
                    }
                }
                return  ctx.suc('', {task: all.toString(), err: err.toString(), suc: suc.toString()})
            }
            case 'isCloudMode': {
                return ctx.suc('',{isCloud: false})
            }
            // 导入任务
            case 'addTask':{
                try {
                    const workbook = xlsx.readFile('./uploads/'+formData.filename);
                    const sheetName = workbook.SheetNames[0]; // 获取第一个工作表的名字
                    const sheet = workbook.Sheets[sheetName]; // 获取第一个工作表
                    const jsonData = xlsx.utils.sheet_to_json(sheet); // 将工作表转换为 JSON
                    if (jsonData && jsonData.length === 0) return ctx.err('没有数据')
                    let list = []
                    for (let i = 0; i < jsonData.length; i++) {
                       if(typeof jsonData[i]['开始日期'] === 'string') {

                       }
                       else if(typeof jsonData[i]['开始日期'] === 'number') {
                           jsonData[i]['开始日期'] = excelDateToJSDate(jsonData[i]['开始日期'])
                       }
                       else {
                           return ctx.err('开始日期格式错误')
                       }
                        if(typeof jsonData[i]['结束日期'] === 'string') {

                        }
                        else if(typeof jsonData[i]['结束日期'] === 'number') {
                            jsonData[i]['结束日期'] = excelDateToJSDate(jsonData[i]['结束日期'])
                        }
                        else {
                            return ctx.err('结束日期格式错误')
                        }
                        let beginDate = tool.xe.trim(tool.xe.toString(jsonData[i]['开始日期']))
                        let endDate = tool.xe.trim(tool.xe.toString(jsonData[i]['结束日期']))
                        if(!beginDate) return ctx.err(`第${i+1}行没有开始日期`)
                        if(!endDate) return ctx.err(`第${i+1}行没有结束日期`)
                        if(tool.dayjs(endDate).diff(tool.dayjs(beginDate)) < 0) {
                            return ctx.err('结束日期不能小于开始日期')
                        }

                        let buCode = tool.xe.trim(tool.xe.toString(jsonData[i]['终端编码']))
                        let buName = tool.xe.trim(tool.xe.toString(jsonData[i]['终端名称']))
                        let terType1 = tool.xe.toNumber(jsonData[i]['出到现饮的数量'])
                        let terType2 = tool.xe.toNumber(jsonData[i]['出到非现饮的数量'])
                        if(buName || buCode) {
                            if(terType1 || terType2) {
                                return  ctx.err('指定了终端就只能填写 到通用数量 或 到赠品数量')
                            }
                        }
                    }
                    for (let i = 0; i < jsonData.length; i++) {
                        if(i > 0 && i%900 === 0) {
                            await db.models['pythonTask'].bulkCreate(list)
                            list = []
                        }
                        let beg = tool.xe.trim(tool.xe.toString(jsonData[i]['开始日期']))
                        let end = tool.xe.trim(tool.xe.toString(jsonData[i]['结束日期']))
                        let buCode = tool.xe.trim(tool.xe.toString(jsonData[i]['终端编码']))
                        let buName = tool.xe.trim(tool.xe.toString(jsonData[i]['终端名称']))
                        let invCode = tool.xe.trim(tool.xe.toString(jsonData[i]['商品编码']))
                        let invName = tool.xe.trim(tool.xe.toString(jsonData[i]['商品名称']))
                        let price = tool.xe.trim(tool.xe.toString(jsonData[i]['价格']))
                        let terType1 = tool.xe.toNumber(jsonData[i]['出到现饮的数量'])
                        let terType2 = tool.xe.toNumber(jsonData[i]['出到非现饮的数量'])
                        let send = tool.xe.toNumber(jsonData[i]['出到赠送的数量'])
                        let qty = tool.xe.toNumber(jsonData[i]['出到通用的数量'])
                        let remark = tool.xe.trim(tool.xe.toString(jsonData[i]['备注']))
                        if((qty + send + terType1 + terType2) !== 0 && invCode) {
                            list.push({
                                username: username,
                                beginDate: beg,
                                endDate: end,
                                buCode,
                                buName,
                                invCode,
                                invName,price,qty,terType1,terType2,send,remark,
                                ct: tool.getTime()
                            })
                        }

                        if (i === jsonData.length -1 && list.length > 0) {
                            await db.models['pythonTask'].bulkCreate(list)
                        }
                    }

                    return ctx.suc('')
                } catch (error) {
                    return ctx.err('上传失败')
                }
            }
            // 任务列表
            case 'taskList': {
                let currentPage = body.currentPage?body.currentPage:1
                let pageSize = body.pageSize?body.pageSize:500
                let skip = (currentPage - 1) * pageSize
                let stat = ''
                if(formData.stat === 'running') {
                    stat = " and a.stat='正常' "
                }
                if(formData.stat === 'stop') {
                    stat = " and a.stat='已停止' "
                }


                let sql = ` from (select a.id,a.username,a.beginDate,a.endDate,isnull(a.qty,0) qty,
                        isnull(a.send,0) send,isnull(a.terType1,0) terType1,isnull(a.terType2,0) terType2,
                        a.buCode,a.buName,a.invCode,a.invName,a.price,a.remark,a.stat
                      ,isnull(b.qty,0) qtyDone,isnull(b.send,0) sendDone,
                 isnull(b.terType1,0) terType1Done,isnull(b.terType2,0) terType2Done from python_task a
                   left join v_python_tasksum b
                  on a.username=b.username and a.id=b.taskId where a.username='${username}' ${stat} ) a where 1=1  `


                if(formData.stat === 'running') {
                    sql = sql + ` and ( (qty - qtyDone) > 0 or (send - sendDone) > 0 or (terType1 - terType1Done) > 0
                     or (terType2 - terType2Done) > 0 ) `
                }
                if(formData.stat === 'done') {
                    stat = " and (qty+send+terType+terType2-qtyDone-sendDone-terType2Done-terType1Done) = 0 "
                }
                if(formData.buName) {
                    sql = sql + ` and a.buName like '%${formData.buName}%' `
                }
                if(formData.invName) {
                    sql = sql + ` and a.invName like '%${formData.invName}%' `
                }
                if(formData.beginDate) {
                    sql = sql + ` and a.beginDate>='${formData.beginDate}'`
                }
                if(formData.endDate) {
                    sql = sql + ` and a.endDate<='${formData.endDate}' `
                }
                let totalsql = 'select count(*) total ' + sql
                let totalModel = await db.query(totalsql, {type: QueryTypes.SELECT, plain: true})

                sql = 'select * ' + sql + ` order by   id desc offset ${skip} rows fetch next ${pageSize} rows only;`
                let list = await db.query(sql, {type: QueryTypes.SELECT})
                list.map(row => {
                    row.qty = tool.xe.toNumber(row.qty) === 0?'':row.qty
                    row.qtyDone = tool.xe.toNumber(row.qtyDone) === 0?'':row.qtyDone
                    row.terType1 = tool.xe.toNumber(row.terType1) === 0?'':row.terType1
                    row.terType2 = tool.xe.toNumber(row.terType2) === 0?'':row.terType2
                    row.terType1Done = tool.xe.toNumber(row.terType1Done) === 0?'':row.terType1Done
                    row.terType2Done = tool.xe.toNumber(row.terType2Done) === 0?'':row.terType2Done
                    row.sendDone = tool.xe.toNumber(row.sendDone) === 0?'':row.sendDone
                    row.send = tool.xe.toNumber(row.send) === 0?'':row.send
                })
                return ctx.suc('', {total: totalModel.total, list})
            }
            // 已完成任务
            case 'taskListHist': {
                let range = formData.stat
                let beginDate = tool.getDay()
                let endDate = tool.getDay()
                if(range === 'month') {
                    beginDate = tool.getDayStart('','beg',0, 'MONTH')
                    endDate = tool.getDayStart('','end',0, 'MONTH')
                }
                if(range === 'lastMonth') {
                    beginDate = tool.getDayStart('','beg',-1, 'MONTH')
                    endDate = tool.getDayStart('','end',-1, 'MONTH')
                }
                let sql = `select * from python_task_today where username='${username}' and 
                                      ym between '${beginDate}' and '${endDate}' `
                if(formData.buName) {
                    sql = sql + ` and buName like '%${tool.xe.trim(formData.buName)}%'`
                }
                if(formData.invName) {
                    sql = sql + ` and invName like '%${tool.xe.trim(formData.invName)}%'`
                }
                let list = await db.query(sql, {type: QueryTypes.SELECT})
                return ctx.suc('', list)
            }
            // 追加一条
            case 'saveOneTask':{
                delete formData._RID
                if(formData.id) {
                    let model = await db.models['pythonTask'].findOne({
                        where: {
                            id: formData.id
                        }
                    })
                    model.qty = formData.qty
                    model.send = formData.send
                    model.terType1 = formData.terType1
                    model.terType2 = formData.terType2
                    model.remark = formData.remark?formData.remark:''
                    if(model.buCode) {
                        if(model.terType1 || model.terType2) {
                            return ctx.err('指定了终端就不用填写 到现饮 或者 到非现饮数量')
                        }
                    }
                    await model.save()
                    return ctx.suc('保存成功')
                }
                else {
                    let buCode = ''
                    let buName = ''
                    let invCode = ''
                    if(!formData.beginDate) return ctx.err('请选择开始日期')
                    if(!formData.endDate) return ctx.err('请选择结束日期')
                    if(tool.dayjs(formData.endDate).diff(tool.dayjs(formData.beginDate)) < 0) {
                        return ctx.err('结束日期不能小于开始日期')
                    }
                    if(formData.buName) {
                        let model = await db.models['pythonTer'].findOne({
                            where: {
                                username,
                                buName: tool.xe.trim(formData.buName)
                            }
                        })
                        if(!model) return ctx.err('没有查询到此终端')
                        buCode = model.buCode
                        if(formData.terType1 || formData.terType2) {
                            return ctx.err('指定了终端就不用填写 到现饮 或者 到非现饮数量')
                        }
                        if(!formData.qty) {
                            return ctx.err('请输入数量')
                        }
                        buName = formData.buName
                    }
                    if(formData.invName) {
                        let model = await db.models['pythonGoods'].findOne({
                            where: {
                                username,
                                invName: tool.xe.trim(formData.invName)
                            }
                        })
                        if(!model) return ctx.err('没有查询到此商品')
                        invCode = model.invCode
                    }
                    else {
                        return ctx.err('没有选择商品')
                    }

                    formData.username = username
                    formData.invCode = invCode
                    formData.buCode = buCode
                    formData.buName = buName
                    formData.ct = tool.getTime()
                    formData.remark = formData.remark?formData.remark:''
                    await db.models['pythonTask'].create(formData)
                    return ctx.suc('保存成功')
                }
            }
            // 删除一条
            case 'delOneTask': {
                let id = ctx.request.body.id
                let has = await db.query(`select count(*) total from python_task_today where taskId=${id}`,
                    {type: QueryTypes.SELECT, plain: true})
                if(has.total > 0) {
                    return ctx.err('此任务已进行，只能停止，不能删除')
                }
                let sql = `delete from python_task where username='${username}' and id=${id} `
                await db.query(sql, {type: QueryTypes.DELETE})
                return ctx.suc('已删除')
            }
            // 停止一条
            case 'stopOneTask': {
                let id = ctx.request.body.id
                let has = await db.query(`update python_task set stat='已停止' where id=${id}`,
                    {type: QueryTypes.SELECT, plain: true})
                return ctx.suc('已删除')
            }
            case 'stopBatchTask': {
                let ids = ctx.request.body.ids
                let has = await db.query(`update python_task set stat='已停止' where id in (${ids.join(',') }) `,
                    {type: QueryTypes.SELECT, plain: true})
                return ctx.suc('已删除')
            }
            // 批量删除
            case 'delBatchTask': {
                let ids = ctx.request.body.ids
                if(ids.length > 0) {

                    for (let i = 0; i < ids.length; i++) {
                        let has = await db.query(`select count(*) total from python_task_today where taskId=${ids[i]}`,
                            {type: QueryTypes.SELECT, plain: true})
                        if(has.total === 0) {
                            let sql =  `delete from python_task where username='${username}' and id=${ids[i]} `
                            await db.query(sql, {type: QueryTypes.DELETE})
                        }
                    }

                    return ctx.suc('已删除')
                }
                return ctx.suc('已删除')
            }
            // 下拉框
            case 'dropdown': {

                let list = []
                if(cat === 'py_ter'){
                    let sql = `select top 20 id,buName name from python_ter where username='${username}' and stat='生效' `
                    if(keyWord) {
                        sql += ` and buName like '%${keyWord}%' `
                    }
                    list = await db.query(sql, {type: QueryTypes.SELECT})
                }
                if(cat === 'py_good'){
                    let sql = `select top 20 id,invName name from python_goods where username='${username}'  `
                    if(keyWord) {
                        sql += ` and invName like '%${keyWord}%' `
                    }
                    list = await db.query(sql, {type: QueryTypes.SELECT})
                }
                return ctx.suc('', list)
            }
            case 'defaultSets1': {
                let setjson = {
                    "version": lastest_version,
                    "brower": "msedge",
                    "autostart": true,
                    "manual": true,
                    "loginurl": "https://csmsuat.crb.cn/",
                    "ckurl": "https://csmsuat.crb.cn/#/outstorehouse/addOutStore",
                    "ckmanurl": "https://csmsuat.crb.cn/#/outstorehouse/outStoreList",
                    "terurl":"https://csmsuat.crb.cn/#/baseinfo/myterminal",
                    "remoteUrl": "http://127.0.0.1:996/",
                    "downUrl": "https://download.sanzhiyun.cn/",
                    "codeUrl": "https://download.sanzhiyun.cn/exec_code.py",
                    "setsUrl": "https://download.sanzhiyun.cn/sets.json",
                    "downName": "pyupdate.zip",
                    "appName": "三智助手.exe",
                    "updateName": "app_update.exe",
                    "printButton":"固定格式单张打印",
                    "printWidth":"241mm",
                    "printHeight": "140mm",
                    "autoVerify": true,
                    "autoPrint": true,
                    "autoArrived": true,
                    "arrivedTime": 10,
                    "cloudMode": false
                }
                return ctx.suc('', setjson)
            }
            case 'defaultSets': {
                let setjson = {
                    "version": lastest_version,
                    "brower": "msedge",
                    "autostart": true,
                    "manual": true,
                    "loginurl": "https://csms.crb.cn/",
                    "ckurl": "https://csms.crb.cn/#/outstorehouse/addOutStore",
                    "ckmanurl": "https://csms.crb.cn/#/outstorehouse/outStoreList",
                    "terurl":"https://csms.crb.cn/#/baseinfo/myterminal",
                    "remoteUrl": "http://127.0.0.1:996/",
                    "downUrl": "https://download.sanzhiyun.cn/",
                    "codeUrl": "https://download.sanzhiyun.cn/exec_code.py",
                    "setsUrl": "https://download.sanzhiyun.cn/sets.json",
                    "downName": "pyupdate.zip",
                    "appName": "三智助手.exe",
                    "updateName": "app_update.exe",
                    "printButton":"固定格式单张打印",
                    "printWidth":"241mm",
                    "printHeight": "140mm",
                    "autoVerify": true,
                    "autoPrint": true,
                    "autoArrived": true,
                    "arrivedTime": 10,
                    "cloudMode": false
                }
                return ctx.suc('', setjson)
            }
            // 配送接口
            case 'savePsSets': {
                try {
                    let tenantId = ctx.user.tenantId
                    let model = await db.models['psSets'].findOne({
                        where: {
                            tenantId
                        }
                    })
                    let str = JSON.stringify(formData)
                    if(model) {
                        model.sets = str
                        await model.save()
                    }
                    else {
                        model = db.models['psSets'].build()
                        model.tenantId = tenantId
                        model.username = ctx.user.username
                        model.sets = str
                        await model.save()
                    }

                    return ctx.suc('保存成功')
                }
                catch (e) {
                    return ctx.err('保存失败')
                }
            }
            case 'getPsSets': {
                try {
                    let tenantId = ctx.user.tenantId
                    let model = await db.models['psSets'].findOne({
                        where: {
                            tenantId
                        }
                    })

                    return ctx.suc('', model)
                }
                catch (e) {
                    return ctx.err('保存失败')
                }
            }
            case 'pssets':
                let tenantId = ctx.user?ctx.user.tenantId:''
                let obj = {
                    "ocr_api_url": "https://api.textin.com/ai/service/v2/recognize/table/multipage",
                    "ocr_api_id": "a2249528c0439f39fc5de8479f215c0c",
                    "ocr_api_code": "5bf919b0a90aaeb24d2557fc724c3e37",
                }
               try {
                   if(username) {
                       let model = await db.query(`select * from ps_sets where username='${username}'`, {type: QueryTypes.SELECT, plain: true})
                       if(model) {
                           let sets = JSON.parse(model.sets)
                           sets.maxArea = tool.xe.toNumber(sets.maxArea)
                           return ctx.suc('', Object.assign(obj, sets))
                       }
                       else
                           return ctx.suc('', '')
                   }
                   else if(tenantId) {
                       let model = await db.query(`select * from ps_sets where tenantId='${tenantId}'`, {type: QueryTypes.SELECT, plain: true})
                       if(model) {
                           let sets = JSON.parse(model.sets)
                           return ctx.suc('', Object.assign(obj, sets))
                       }
                       else
                           return ctx.suc('', '')
                   }
               }
               catch (e) {
                    console.error(e)
                    return ctx.err('')
               }
            case 'updater': {
                let kind = body.kind
                if(kind === 'deliver') {
                    return ctx.suc('', {
                        title: '三智配送',
                        exeZh: '三智配送.exe',
                        exeEn: 'deliver.exe',
                        downName: 'szpsUpdate.zip'
                    })
                }
                return ctx.err('没有参数')
            }
            case 'deliverVer': {
                return ctx.suc('', "1.0.1")
            }

            default:
                return ctx.err('没找到相关业务')
        }
    }
    catch (e) {
        return ctx.err(e)
    }

})

const excelDateToJSDate = (serial) => {
    // If serial is less than 60, it's probably a 1900 date, add 1 to correct.
    if (serial < 60) {
        serial += 1;
    }
    // Convert to UTC days then to seconds
    const utc_days  = Math.floor(serial - 25569); // Changed from -25569 to -25568 to correct for the day difference
    const utc_value = utc_days * 86400;
    const date_info = new Date(utc_value * 1000);

    // Handle the fractional part for time
    const fractional_day = serial - Math.floor(serial) + 0.0000001;
    let total_seconds = Math.floor(86400 * fractional_day);

    const seconds = total_seconds % 60;
    total_seconds -= seconds;

    const hours = Math.floor(total_seconds / (60 * 60));
    const minutes = Math.floor(total_seconds / 60) % 60;

    // Create a new date object, adding the time component
    let dat = new Date(date_info.getFullYear(), date_info.getMonth(), date_info.getDate(), hours, minutes, seconds);
    return tool.dayjs(dat).format('YYYY-MM-DD')
}

module.exports = router;
